package com.citywithincity.db;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.citywithincity.api.ApiConfig;

import oracle.jdbc.driver.OraclePreparedStatement;


public class SqlHelper {

	
	public static int setPs(PreparedStatement ps, List<EntityField> updatedFields, List<Object> updatedValues) {
		int index = 1;
		for (EntityField object : updatedFields) {
			try{
				Object value = updatedValues.get(index-1);
				object.psSetter.setValue(ps, index, value);
				++index;
			}catch(SQLException e){
				throw new DBException(e);
			}
		}
		
		
		
		return index;
	}
	

	
	public static int setPs(PreparedStatement ps, Object data, EntityField[] entityFields) throws SQLException {
		int index = 1;
		for (EntityField field : entityFields) {
			field.setPs(ps,index++,data);
		}
		return index;
	}
	
	public static StringBuilder makeInsertReturning(
			List<EntityField> insertFields,
			List<Object> insertValues,
			String tableName,
			EntityField[] fields,
			EntityField[] uniqueFields,
			
			Object data,
			String id){
		StringBuilder sql = makeInsert(insertFields,insertValues, tableName, fields, uniqueFields, data);
		sql.append(" RETURNING ").append(id).append(" INTO ?");
		return sql;
	}
	
	
	public static StringBuilder makeOracleInsert(
			List<EntityField> insertFields,
			List<Object> insertValues,
			String tableName,
			EntityField[] fields,
			EntityField[] uniqueFields,
			Object data){
		StringBuilder sql = new StringBuilder(100);
		sql.append("INSERT INTO ").append(tableName).append(" (");
		//首先去掉空的
		//first part
		boolean first = true;
		int count =0;
		for (EntityField entityField : fields) {
			Object value = entityField.get(data);
			if(value!=null){
				insertValues.add(value);
				insertFields.add(entityField);
				if(first){
					first = false;
				}else{
					sql.append(',');
				}
				sql.append(entityField.columnName);
				++count;
			}
		}
		
		if(uniqueFields!=null){
			sql.append(") SELECT ?");
			for(int i=1; i < count; ++i){
				sql.append(",?");
			}
			sql.append(" FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ")
			.append(tableName).append(" WHERE ");
			first = true;
			for (EntityField key : uniqueFields) {
				if(first){
					first = false;
				}else{
					sql.append(" AND ");
				}
				sql.append(key.columnName).append("=?");
			}
			sql.append(")");
		}else{
			sql.append(") VALUES (?");
			for(int i=1; i < count; ++i){
				sql.append(",?");
			}
			sql.append(')');
		}
		return sql;
	}

	
	public static StringBuilder makeInsert(
			List<EntityField> insertFields,
			List<Object> insertValues,
			String tableName,
			EntityField[] fields,
			EntityField[] uniqueFields,
			Object data){
		StringBuilder sql = new StringBuilder(100);
		sql.append("INSERT INTO ").append(tableName).append(" (");
		//首先去掉空的
		//first part
		boolean first = true;
		int count =0;
		for (EntityField entityField : fields) {
			Object value = entityField.get(data);
			if(value!=null){
				insertValues.add(value);
				insertFields.add(entityField);
				if(first){
					first = false;
				}else{
					sql.append(',');
				}
				sql.append(entityField.columnName);
				++count;
			}
		}
		
		if(uniqueFields!=null){
			sql.append(") SELECT ?");
			for(int i=1; i < count; ++i){
				sql.append(",?");
			}
			sql.append(" FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ")
			.append(tableName).append(" WHERE ");
			first = true;
			for (EntityField key : uniqueFields) {
				if(first){
					first = false;
				}else{
					sql.append(" AND ");
				}
				sql.append(key.columnName).append("=?");
			}
			sql.append(")");
		}else{
			sql.append(") VALUES (?");
			for(int i=1; i < count; ++i){
				sql.append(",?");
			}
			sql.append(')');
		}
		return sql;
	}
	
	public static int executeInsert(Object data,OraclePreparedStatement ps,String sql, EntityField[] uniqueFields, List<EntityField> insertFields, List<Object> insertValues,boolean returning) throws SQLException {
		int index = SqlHelper.setPs(ps,insertFields,insertValues);
		if(uniqueFields!=null){
			for(int i=0 ,c = uniqueFields.length; i < c ; ++i){
				EntityField entityField = uniqueFields[i];
				entityField.setPs(ps, index++, data);
			}
		}
		ps.registerReturnParameter(index, Types.BIGINT);
		return ps.executeUpdate();
	}

	
	public static String replaceStatement(Object data,String sql, EntityField[] uniqueFields, List<EntityField> insertFields, List<Object> insertValues){
		String format = sql.replace("?", "'%s'");
		Object[] values = new Object[insertValues.size() + (uniqueFields == null ? 0 : uniqueFields.length )];
		int index = 0;
		for (Object value : insertValues) {
			values[index++]=String.valueOf(value);
		}
		if(uniqueFields!=null){
			for(int i=0 ,c = uniqueFields.length; i < c ; ++i){
				EntityField entityField = uniqueFields[i];
				Object value = entityField.get(data);
				values[index++]=String.valueOf(value);
			}
		}
		return String.format(format, values);
	}
	
	public static int executeInsert(PreparedStatement ps,String sql,Object entity, EntityField[] uniqueFields, List<EntityField> insertFields, List<Object> insertValues) throws SQLException {
		int index = SqlHelper.setPs(ps,insertFields,insertValues);
		if(uniqueFields!=null){
			for(int i=0 ,c = uniqueFields.length; i < c ; ++i){
				uniqueFields[i].setPs(ps, index++, entity);
			}
		}
		try{
			return ps.executeUpdate();
		}catch(SQLException e){
			logSql(sql, entity, uniqueFields, insertFields, insertValues);
			throw e;
		}
		
	}
	
	private static final Log logger = LogFactory.getLog(SqlHelper.class);
	
	protected static void logSql(String sql,Object entity, EntityField[] uniqueFields, List<EntityField> insertFields, List<Object> insertValues){
		if(!ApiConfig.debug())return;
		String s = sql.toString();
		
		int len = insertFields.size();
		if(uniqueFields!=null){
			len += uniqueFields.length;
		}
		
		Object[] args = new Object[len];
		
		int index = 0;
		for (Object value : insertValues) {
			args[index++] = value;
		}
		
		if(uniqueFields!=null){
			for (EntityField fEntityField : uniqueFields) {
				args[index++] = fEntityField.get(entity);
			}
		}
		
		s = s.replace("?", "'%s'");
		logger.info(String.format(s, args));
	}

	public static void makeUpdate(StringBuilder sql, String tableName, List<EntityField> updatedFields) {
		sql.append("UPDATE ").append(tableName);
		boolean first = true;
		for (EntityField field : updatedFields) {
			if(first){
				first = false;
				sql.append(" SET ");
			}else{
				sql.append(',');
			}
			sql.append(field.columnName).append("=?");
		}
	}
	
	public static void makeUpdate(StringBuilder sql,String tableName, EntityField[] updateFields) {
		sql.append("UPDATE ").append(tableName);
		boolean first = true;
		for (EntityField field : updateFields) {
			if(first){
				first = false;
				sql.append(" SET ");
			}else{
				sql.append(',');
			}
			sql.append(field.columnName).append("=?");
		}
	}


	
	


}
